import pymysql,configparser,os
from datetime import date, datetime, timedelta
import mdb_connect
from apscheduler.schedulers.blocking import BlockingScheduler

#连接配置信息
config = {
          'host':'47.113.124.50',
          'port':3306,
          'user':'root',
          'password':'yongs81',
          'db':'hisense',
          'charset':'utf8mb4',
          'cursorclass':pymysql.cursors.DictCursor,
          }


# 打开数据库连接
def mysql_connect():
    try:
        db = pymysql.connect(**config)
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        return  db, cursor
    except:
        return False

# 插入数据
def mysql_insert(conn, cursor,line,data):
    i = 1
    for d in data:
        if mysql_get(cursor,d):
            continue
        else:
            AlarmDate, AlarmTime, AlarmType, VarName = d
            sql = "INSERT  IGNORE INTO pause(line, AlarmDate, AlarmTime, AlarmType, VarName, DateTime) values(%s, %s, %s, %s, %s, %s)"
            time = AlarmDate.strip() + " " + AlarmTime.strip()
            cursor.execute(sql,(line, AlarmDate, AlarmTime, AlarmType, VarName, time))
            i += 1
            conn.commit()
            if i%10 == 0:
                print('已完成{0}个数据上传'.format(i))
    conn.close()

# def mysql_select(conn,cursor,set=[ 'line','AlarmDate', 'AlarmTime', 'AlarmType', 'VarName', 'DateTime']):
#     sql = "SELECT {0}, {1}, {2}, {3}, {4}, {5} FROM pause WHERE {0}={6}, {1}={7},".format(set[0],set[1],set[2],set[3],set[4],set[5])
#     cursor.execute(sql)
#     data = cursor.fetchall()
#     return data

# 获得pause表里面跟set数据集合一样的数据
def mysql_get(cursor, set):
    sql = "SELECT AlarmDate,AlarmTime,AlarmType,VarName FROM pause WHERE AlarmDate='{0}' AND AlarmTime='{1}'AND AlarmType='{2}'AND VarName='{3}'".format(set[0],set[1],set[2],set[3])
    cursor.execute(sql)
    data = cursor.fetchall()
    return data


def storage(line, path, interval):
    # 连接MySQL数据库
    conn, cursor = mysql_connect()
    # 连接本地access数据库
    # pathfile = r'C:\Users\hyq\Desktop\w7.mdb'
    access_conn = mdb_connect.mdb_conn(path)
    access_cur = access_conn.cursor()
    # 确定表的名字
    month = datetime.today().month
    tablename = 'Alarm20200{0}'.format(month)
    # 取得表的数据
    if interval is None or interval == "":
        mdb_sql = "SELECT DISTINCT AlarmDate,AlarmTime,AlarmType,VarName FROM {0} ORDER BY AlarmDate, AlarmTime".format(
            tablename)
    else:
        daytime = datetime.now() - timedelta(minutes=int(interval))
        day = daytime.date().__format__('%y/%m/%d')
        time = daytime.time().__format__('%H:%M:%S')
        mdb_sql = "SELECT DISTINCT AlarmDate,AlarmTime,AlarmType,VarName FROM {0} WHERE AlarmDate LIKE '{1}' AND AlarmTime >= '{2}' ORDER BY AlarmDate, AlarmTime".format(
            tablename, day, time)
    mdb_data = mdb_connect.mdb_sel(access_cur, mdb_sql)
    # 插入远程mysql数据库
    mysql_insert(conn, cursor, line, mdb_data)


if __name__ == '__main__':
    # 取得参数
    conf = configparser.ConfigParser()
    if os.path.exists('config.ini'):
        conf.read('config.ini')
        line = conf['DEFAULT']['line']
        path = conf['DEFAULT']['path']
        interval = int(conf['DEFAULT']['interval'])
    else:
        line = input('请输入线体：')
        path = input('请输入数据库的位置：')
        interval = input('请输入时间间隔（分钟）:')
        conf["DEFAULT"] = {'line': line,
                             'path': path,
                             'interval': interval
                             }
        interval = ""
        with open('config.ini', 'w') as f:
            conf.write(f)
    scheduler = BlockingScheduler()
    scheduler.add_job(storage, 'interval', minutes=3, args=[line, path, interval])
    scheduler.start()
